package dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import util.Debug;

import dao.interfaces.IUserDAO;

import model.Recipe;
import model.User;

public class UserDAO extends DAO<User> implements IUserDAO<User> {
	//private static Connection connection = DBConnection.getInstance();
	
	public UserDAO(Connection conn) {
		super(conn);
	}
	
	/**
	 * implements the inherited abstract methods from the DAO<User> abstract class
	 */
	
	@Override
	public boolean insert(User aUser) {

		String sql =
			"INSERT INTO cook_user " +
			"(user_firstname, user_lastname, user_login, " +
			"user_email, user_address, user_password, " +
			"user_group, user_gender, user_date, user_date_last_conn) " +
			"VALUES ('" +
			aUser.getFirstname().replace("'", "''") + "', '" + aUser.getLastname().replace("'", "''") + "', '" + aUser.getLogin().replace("'", "''") + "', '" +
//			aUser.getEmail() + "', '" + aUser.getAddress() + "', MD5('" + aUser.getPassword() + "'), " +
			aUser.getEmail().replace("'", "''") + "', '" + aUser.getAddress().replace("'", "''") + "', '" + aUser.getPassword() + "', " +
			aUser.getGroup() + ", " +	aUser.getGender() + ", DATE(NOW()), NOW()" +
			")";
			//default user group is 0
		Debug.log(sql);
		try {
			Statement stm = connection.createStatement();
			stm.executeUpdate(sql);
			stm.close();
			return true;
			} 
		catch (SQLException e) {
			e.printStackTrace();
			return false;
		}	
	}
	
	@Override
	public boolean delete(User user) {
		return false;
	}
	
	@Override
	public boolean update(User user) {
//		String pwdUpdateStr = (user.getPassword() != null && !user.getPassword().equals(""))
//								? " user_password = MD5('" + user.getPassword() + "'), " 
//								: "";
		String sql = 
			"UPDATE cook_user SET user_firstname = '"+ user.getFirstname() + "', " +
			"user_lastname = '" + user.getLastname() + "', " +
			"user_login = '" + user.getLogin() + "', " +
			"user_email = '" + user.getEmail() + "', " +
			"user_address = '" + user.getAddress() + "', " +
//			pwdUpdateStr +
			"user_password = '" + user.getPassword() + "', " +
			"user_gender =  " +  user.getGender() + ", " +
			"user_date_last_conn = NOW() " +
			"WHERE user_id =" + user.getId();
		System.out.println(sql);
		Debug.log(sql);
		try {
			Statement stm = connection.createStatement();
			stm.executeUpdate(sql);
			stm.close();
			return true;
			
			} catch (SQLException e) {
				e.printStackTrace();
				return false;
			}
	}
	
	@Override
	public User findById(int user_id) {
		String sql =
			"SELECT * " +
			"FROM cook_user " +
			"WHERE user_id = " + user_id;
		Debug.log(sql);
		try{
			Statement stm = connection.createStatement();
			ResultSet rs = stm.executeQuery(sql);
			
			if (rs.next()) {
				User user = new User();
				user.setId(rs.getInt("user_id"));
				user.setLogin(rs.getString("user_login"));
				user.setPassword(rs.getString("user_password")); 
				user.setFirstname(rs.getString("user_firstname"));
				user.setLastname(rs.getString("user_lastname"));
				user.setEmail(rs.getString("user_email"));
				user.setAddress(rs.getString("user_address"));
				user.setGroup(Integer.parseInt(rs.getString("user_group")));
				user.setGender(Integer.parseInt(rs.getString("user_gender")));
				user.setDate(rs.getString("user_date"));
				user.setDate_last_conn(rs.getString("user_date_last_conn"));
				return user;
			}	
			stm.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	@Override
	public List<User> getAll() {
		List<User> userList = new ArrayList<User>();
		try{
			Statement stm = connection.createStatement();
			ResultSet rs = stm.executeQuery("SELECT * FROM cook_user");
			
			while (rs.next()) {
				User user = new User();
				user.setId(rs.getInt("user_id"));
				user.setLogin(rs.getString("user_login"));
				user.setPassword(rs.getString("user_password")); 
				user.setFirstname(rs.getString("user_firstname"));
				user.setLastname(rs.getString("user_lastname"));
				user.setEmail(rs.getString("user_email"));
				user.setAddress(rs.getString("user_address"));
				user.setGroup(Integer.parseInt(rs.getString("user_group")));
				user.setGender(Integer.parseInt(rs.getString("user_gender")));
				user.setDate(rs.getString("user_date"));
				user.setDate_last_conn(rs.getString("user_date_last_conn"));

				userList.add(user);
			}	
			stm.close();
			return userList;
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * implements the inherited abstract methods from the IUserDAO<User> interface
	 */
	
	public User authenticateUser(String login, String password) {
	
		String sql =
			"SELECT * " +
			"FROM cook_user " +
			"WHERE user_login = '" + login + "' " +
			"AND user_password = '" + password + "'";
//			"AND user_password = MD5('" + aUser.getPassword() + "')";
		Debug.log(sql);
		try {
			Statement stm = connection.createStatement();
			ResultSet rs = stm.executeQuery(sql);
			
			if (rs.next()) {
				User user = new User();
				user.setId(rs.getInt("user_id"));
				user.setLogin(rs.getString("user_login"));
				user.setPassword(rs.getString("user_password"));
				user.setFirstname(rs.getString("user_firstname"));
				user.setLastname(rs.getString("user_lastname"));
				user.setEmail(rs.getString("user_email"));
				user.setAddress(rs.getString("user_address"));
				user.setGroup(Integer.parseInt(rs.getString("user_group")));
				user.setGender(Integer.parseInt(rs.getString("user_gender")));
				user.setDate(rs.getString("user_date"));
				user.setDate_last_conn(rs.getString("user_date_last_conn"));
				return user;
			}
			
			stm.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public Integer getUserNumbers() {
		if (getAll() == null)
			return 0;
		return getAll().size();
	}
}
